package com.wanxi.dao.impl;

import com.wanxi.dao.UserDao;
import com.wanxi.model.UserModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
//        返回数据库查询的数据，有可能是一条，有可能没有
//        需要解析ResultSet
        ResultSet query = JDBC.query(sql);
        UserModel model = null;
        try {
            while (query.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return model;
    }

    @Override
    public int add(UserModel model) {
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`, " +
                "`phone`, `sex`, " +
                "`hobby`, `birthday`, `remark`) VALUES (" +
                "'" + model.getUsername() + "', '" + model.getAddress() + "', now()," +
                " '" + model.getEmail() + "', '" + model.getEnable() + "', '" + model.getPassword() + "'," +
                " '" + model.getPhone() + "', '" + model.getSex() + "', " +
                "'', '" + model.getBirthday() + "', '');";

        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel model) {
        String sql = "UPDATE `user` SET " +
                "`username` = '" + model.getUsername() + "', " +
                "`address` = '" + model.getAddress() + "', " +
                " `enable` = '" + model.getEnable() + "'," +
                "`phone` = '" + model.getPhone() + "', " +
                "`sex` = '" + model.getSex() + "', " +
                "`update_time` = now()," +
                " `birthday` = '" + model.getBirthday() + "' " +
                "WHERE (`id` = '" + model.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public int updatePassword(UserModel model) {
        String sql = "update user set password='" + model.getPassword() + "' where id=" + model.getId();
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(UserModel model) {
        String sql = "select *,date_format(birthday,'%Y-%m-%d') temp from user where id=" + model.getId();
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                UserModel userModel = new UserModel();
                userModel.setUsername(query.getString("username"));
                userModel.setId(query.getInt("id"));
                userModel.setPhone(query.getString("phone"));
                userModel.setEmail(query.getString("email"));
                userModel.setAddress(query.getString("address"));
                userModel.setBirthday(query.getString("temp"));
                userModel.setSex(query.getString("sex"));
                userModel.setHobby(query.getString("hobby"));
                userModel.setEnable(query.getInt("enable"));
                userModel.setCreateTime(query.getString("create_time"));
                return userModel;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int count(UserModel model) {
        String sql = "select count(*) count from user where 1=1 ";
        sql += getSql(model);
        return JDBC.count(sql);
    }

    private String getSql(UserModel model) {
        String sql = "";
        if (!"".equals(model.getUsername())) {
            sql += " and username like '%" + model.getUsername() + "%'";
        }
        if (!"".equals(model.getEmail())) {
            sql += " and email like '%" + model.getEmail() + "%'";
        }
        if (model.getEnable() != -1) {
            sql += " and enable =" + model.getEnable();
        }
        if (!"".equals(model.getSex())) {
            sql += " and sex='" + model.getSex() + "'";
        }
        if (!"0000-00-00".equals(model.getStart()) && "0000-00-00".equals(model.getEnd())) {
            sql += " and birthday >='" + model.getStart() + "' ";
        }
        if (!"0000-00-00".equals(model.getEnd()) && "0000-00-00".equals(model.getStart())) {
            sql += " and birthday <='" + model.getEnd() + "' ";
        }
        if (!"0000-00-00".equals(model.getEnd()) && !"0000-00-00".equals(model.getStart())) {
            sql += " and birthday between'" + model.getStart() + "' and '" + model.getEnd() + "' ";
        }
        return sql;
    }

    @Override
    public UserModel findByIdAndPassword(UserModel model) {
        String sql = "select * from user where id=" + model.getId() + " and password='" + model.getPassword() + "'";
        ResultSet query = JDBC.query(sql);
        UserModel userModel = null;
        try {
            while (query.next()) {
                userModel = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userModel;
    }

    @Override
    public int delete(UserModel model) {
        String sql = "delete from user where id=" + model.getId();
        return JDBC.update(sql);
    }

    @Override
    public List<UserModel> findAll(UserModel model) {
        String sql = "select *,date_format(birthday,'%Y-%m-%d') temp from user where 1=1 ";
        sql += getSql(model);
        sql += " order by id desc";
        sql += " limit " + (model.getPage() + -1) * model.getLimit() + "," + model.getLimit() + " ";
        ResultSet query = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();

        try {
            while (query.next()) {
                UserModel userModel = new UserModel();
                userModel.setUsername(query.getString("username"));
                userModel.setId(query.getInt("id"));
                userModel.setPhone(query.getString("phone"));
                userModel.setEmail(query.getString("email"));
                userModel.setAddress(query.getString("address"));
                userModel.setBirthday(query.getString("temp"));
                userModel.setSex(query.getString("sex"));
                userModel.setHobby(query.getString("hobby"));
                userModel.setEnable(query.getInt("enable"));
                userModel.setCreateTime(query.getString("create_time"));

                list.add(userModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
